/**
 * Nikita van Wijk
**/

import processing.core.*;
import java.sql.Timestamp;
import java.util.*;
import java.sql.*;



public class GraphSongRatings extends PApplet implements TLModule
{
	Connection connection;
	Boolean geenSongs = true;
	ResultSet rs;
	float maxTweets=0;
	
	List songTitle = new LinkedList();
	
		
	public GraphSongRatings() 
	{
		// Connectie maken met de database
    	try 
        {
    		// Load the JDBC driver
        	String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver 
        	
        	Class.forName(driverName);
        	// Create a connection to the database
        	String serverName = "localhost";
        	String mydatabase = "infviz";
        	String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
        	String username = "root";
        	String password = "augmented";
        	
        	// maak connectie
        	connection = DriverManager.getConnection(url, username, password);
        	System.out.println("Connected to the database");
        	
        	   		
       		
    	           	
        } 
        catch (ClassNotFoundException e) 
        {
        	// Could not find the database driver
        	System.out.println("Could not find the database driver");
        	System.err.println(e.getMessage());
        } 
        catch (SQLException e) 
        {
        	// Could not connect to the database
        	System.out.println("Could nog connect to the database");
        }
        
   	}
  	
  public void setup() 
   	{
    	size(380,175);
  		background(255); // achtergrond white
  		

  		stroke(0); //lijnkleur zwart
  		strokeWeight(3); // Set line width to 3 pixels
		smooth(); // Smooth line edges
  		
  		translate(30,30);
  		drawAxes();
  		
    }
  
  public void draw() 
  {
		translate(30,30);
		chart();
  }

  	
	// maken van de assen en de tekst hierbij
	private void drawAxes()
	{
  		stroke(156,156,156);
		strokeWeight(1); 
  		
  		// teken de assen
  		line(0,0,0,ysize);
  		line(0,ysize,xsize,ysize);
  		
  		// schrijf de teksten langs de assen
 		PFont myfont;
  		int fontSize = 10;
  		fill(0);
  		myfont = createFont("Ariel", fontSize);
  		textFont(myfont);
  		
  		PFont myfont2;
  		fontSize = 12;
  		fill(0);
  		myfont2 = createFont("Ariel", fontSize);
  		textFont(myfont2);
  		
  		
  		textFont(myfont);
  		// tekst voor langs de x-as --> een datum van een week is beter, dus nog vervangen voor de getallen 0 t/m 4
  		text("24/04", 1, ysize + fontSize);
  		text("01/05", 67, ysize + fontSize);
  		text("08/05", 133, ysize + fontSize);
  		text("15/05", 195, ysize + fontSize);
  		
  		
  		text("Date", 170, ysize + fontSize+12);
  		
  		pushMatrix();
  		rotate(3 * TWO_PI / 4.0f);
  		text("0", -100, -5);
  		text("50", -50, -5);
  		text("100", -10, -5);
  		//tekst langs de y-as
  		text("Rating", -40, -18);
  		
  		popMatrix();
	}
	
	private void chart()
	{  
		stroke(0); //lijnkleur zwart
  		strokeWeight(1); // Set line width to 1 pixels
		smooth(); // Smooth line edges
		noFill(); //gedeelte onder de grafiek niet gevuld
		
		
		PFont font;
  		int fontSize = 10;
  		
  		font = createFont("Ariel", fontSize);
  		textFont(font);
  		
 	
  		float[] leeg = {0};
    	float[] grafiekWaarden =leeg;
    	float[] waardenBB =leeg;
    	
    	if(geenSongs)
    		{    			
       		}
    	else
    		{
    				String titel = (String)songTitle.get(0);
    				text("song= " + titel, 220,15);
    			
    				
    				waardenBB = getBBWaarden(songTitle.get(0));
       				stroke(234,234,0);
       				line(0,100 - waardenBB[0], 67, 100 - waardenBB[1]);
       				line(67, 100 - waardenBB[1], 133, 100 - waardenBB[2]);
       				line(133, 100 - waardenBB[2], 200, 100 - waardenBB[3]);
       				
       				line(220, 30, 230, 30);
        			text("Billboard", 233, 32);
        			
       				stroke(0,0,255,255);
       			  	fill(0,0,255,100);
       				grafiekWaarden = getDateTweets(songTitle.get(0));
       				
       				beginShape();
       			 
       			    vertex(0, (100-((grafiekWaarden[0]/maxTweets) * 25)));
       			    vertex(67, (100-((grafiekWaarden[1]/maxTweets) * 25)));
       			    vertex(133, (100-((grafiekWaarden[2]/maxTweets) * 25)));
       			    vertex(200, (100-((grafiekWaarden[0]/maxTweets) * 25)));
       			    vertex(xsize, ysize);
       			    vertex(0, ysize);
       			
       			    endShape(CLOSE);
       			    line(220, 40, 230, 40);
     				text("Twitter", 233, 42);
       			    	  		
    		}
    			geenSongs=true;    			
    		
     			
    	}	
	

	

    public float[] getDateTweets(Object songTitle)
    {
    	List tweetList = new ArrayList();
    	int[] aantalTweets = new int[26];
    	try
    	{
    		String query = "select date, t2.nr_tweets from timeline left join (select date, sum(nr_tweets) nr_tweets from phrase_popularity join (select distinct phrase_id from lyrics where song_title = '" + songTitle + "') t1 using (phrase_id) group by date) t2 using (date) order by date";
    		Statement stmt = connection.createStatement();
    		rs= stmt.executeQuery(query);
    		
    		int i=0;
    	
    		while(rs.next())
    		{
    			String date = rs.getString("date");
    			int tweets = rs.getInt("nr_tweets");
    			//System.out.println("tweets: " +tweets);
    			aantalTweets[i] = tweets;
    			i++;
    			
    		}	
		
    	}
    	catch (SQLException e) 
    	{
    		// Could not connect to the database
    		System.out.println("Could nog connect to the database");
    	
    	}
    	float[] tweetsDate = new float[4];
    	
    	
    	tweetsDate[0] = (aantalTweets[1] + aantalTweets[2] + aantalTweets[3] + aantalTweets[4] + aantalTweets[5] + aantalTweets[6] + aantalTweets[7])/7;
    	tweetsDate[1] = (aantalTweets[11] + aantalTweets[12] + aantalTweets[13] + aantalTweets[14])/4;
    	tweetsDate[2] = (aantalTweets[15] + aantalTweets[16] + aantalTweets[17] + aantalTweets[18] + aantalTweets[19] + aantalTweets[20])/6;
    	tweetsDate[3] = (aantalTweets[21] + aantalTweets[22] + aantalTweets[23] + aantalTweets[24] +aantalTweets[25])/5;
     	System.out.println("tweetsDate0: " + tweetsDate[0]);
     	System.out.println("tweetsDate1: " + tweetsDate[1]);
     	System.out.println("tweetsDate2: " + tweetsDate[2]);
     	System.out.println("tweetsDate3: " + tweetsDate[3]);
    	
    	
    	for(int j=0; j<4; j++)
    	{
    		if (tweetsDate[j] > maxTweets)
    			maxTweets=tweetsDate[j];
    	}
    	
    	return tweetsDate;
    }

    
    public float[] getBBWaarden(Object songTitle)
    {
    	float[] bbWaarden = new float[100];
    	String date = "";
    	for(int i=0; i<4; i++)
    	{
    		if(i==0)
    		date = "2010-04-24";
    		if(i==1)
    		date = "2010-05-01";
    		if(i==2)
    		date = "2010-05-08";
    		if(i==3)
    		date = "2010-05-15";
    			
    		
    		try
    		{
    			String query = "SELECT ranking FROM ranking WHERE song_title = '"+ songTitle +"' and chart_name = 'hot-100' and chart_date = '" + date+ "' ";
    			Statement stmt = connection.createStatement();
    			rs = stmt.executeQuery(query);
    			
    		
    			while(rs.next())
    			{
    				int rank = rs.getInt("ranking");
    				System.out.println("rank= " + rank);
    				bbWaarden[i] = rank;
    				  			
    			}	
    			
		
    		}
    		catch (SQLException e) 
    		{
    			// Could not connect to the database
    			System.out.println("Could nog connect to the database");
    		}
    	}
    	
    	for(int j=0; j<4; j++)
    	{
    		if (bbWaarden[j] > 100)
    			bbWaarden[j]= 100;
    	}
    	return bbWaarden;
    }
       
    public void reset()
    {
    	geenSongs = true;
    	// lege grafiek teruggeven, zonder dat er songs inzitten. Deze method is hetzelde als resetSong()
    }
	
	public void setUser(List<String> users)
	{
			
	}
	
	public void highlightUser(List<String> users)
	{
		
	}
	
	public void resetUser()
	{
	}
	
	public void setTimeSpan(Timestamp start, Timestamp end)
	{
	}
	
	public void resetTime()
	{
	}
	
	public void setSong(List<Long> songId)
	{
		geenSongs = false;
		
		songTitle = putSongInList(songId);
	}
	
	
	
	public void resetSong()
	{
		// lege grafiek terug geven, zonder dat er songs inzitten
		geenSongs = true;
	}
	
	public void highlightSong(List<Long> songId)
	{
		
	}
	
	public void setPhrase(List<Long> phraseId)
	{
	}
	
	public void highlightPhrase(List<Long> phraseId)
	{
	}
	
	public void resetPhrase()
	{
	}
	
	
	public List putSongInList(List<Long> songId)
	{
		List songs = new LinkedList();
		{
			try
	    	{
				
	    		Statement stmt = connection.createStatement();
	    		rs = stmt.executeQuery("SELECT song_title FROM song WHERE song_id =" + songId.get(0));
	    		
	    		
	    		while(rs.next())
	    		{
	    		String name = rs.getString("song_title");
	    		songs.add(name);
	    		
	    		}
	    		
	    	}
	    	catch (SQLException e) 
	        {
	        	// Could not connect to the database
	        	System.out.println("Could nog connect to the database");
	        	
	        }
	    	
		}
		return songs;
	}
	
    
    private int xsize = 200;
	private int ysize = 100;


	@Override
	public void resetBillBoard(String chart_name) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setBillBoard(String chart_name) {
		// TODO Auto-generated method stub
		
	}
	
	
}
